package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.Banner;
import com.ysd.util.DBManager;

public class BannerDaoImpl { // 实现类,起名: 实体类名DaoImpl
	public List<Banner> getBanner() {
		List<Banner> list = new ArrayList<Banner>();
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from Banner";
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int bannerId = rs.getInt("bannerId");
				String bannerImg = rs.getString("bannerImg");
				Banner banner = new Banner(bannerId, bannerImg);
				list.add(banner);
			}
			return list;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}

	/**
	 * 根据图片id查询图片
	 * 
	 * @param cId 要删除的图片的id
	 * @return 数据库受影响行数
	 */
	public int deleteBanner(int cId) {
		return DBManager.executeUpdate("delete from banner where bannerId=" + cId);
	}
	
	/**
	 * 添加轮播图
	 * @param ba
	 * @return
	 */
	public int addBanner(Banner ba) {
		return DBManager.executeUpdate("insert into banner values (null,'" + ba.getBannerImg() + "')");
	}

	/**
	 * 通过路径查询图片
	 * @param img
	 * @return
	 */
	public List<Banner> BannerImg(String img) {

		List<Banner> baList = new ArrayList<Banner>();

		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from banner where bannerImg='" + img + "'");
			while (rs.next()) {
				int baId = rs.getInt("bannerId");
				String baImg = rs.getString("bannerImg");
				Banner ba = new Banner(baId, baImg);
				baList.add(ba);
			}
			return baList;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}

	}
	public Banner getBannerId(int id) {
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs =null;
		
		Banner ba  = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from banner where bannerId="+id);
			while(rs.next()) {
				int baId = rs.getInt("bannerId");
				String baImg = rs.getString("bannerImg");
			    ba =new Banner(baId,baImg);
				
			}
			return ba;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
		
	}
	public int updateBanner(Banner ban ) {
		String sql = "update banner set bannerImg='"+ban.getBannerImg()+"' where bannerId="+ban.getBannerId();
	    System.out.println(sql);
        return DBManager.executeUpdate(sql);
	}
	/***
	 *根据id批量删除轮播图
	 *返回受影响行数 int
	 *
	 *
	 */
	public int delBannerByIds(String ids) {
		return DBManager.executeUpdate("delete from banner where bannerId in("+ids+")");
	}

}
